﻿Imports System.Data
Imports System.Data.Odbc

Module dataConceptoDeGastos
    Private dtConceptoG As New DataTable

    Sub datosCBGrupoGastos()

        conectar()
        Dim dt2 As New DataTable

        cmd = New OdbcCommand("SELECT ID, Nombre FROM  tgruposdegastos", con)
        dr = cmd.ExecuteReader
        'executing the command and assigning it to connection 

        dt2.Load(dr, LoadOption.OverwriteChanges)
        With frmConceptosDeGastos.cbGrupoGastos
            .DataSource = dt2
            .DisplayMember = dt2.Columns.Item(1).Caption
            .ValueMember = dt2.Columns.Item(0).Caption
        End With

        desconectar()

    End Sub



    Sub listaConceptoGastos()

        conectar()
        dtConceptoG.Clear()

        cmd.Connection = con
        cmd.CommandText = "SELECT c.ID, c.Nombre, c.Alias, c.Valor, g.Nombre  FROM tconceptosdegastos  c, tgruposdegastos  g WHERE c.IDGruposDeGastos = g.ID"
        cmd.CommandType = CommandType.Text
        da.SelectCommand = cmd
        da.Fill(dtConceptoG)

        'Çargamos el Datagridview
        With frmConceptosDeGastos.DGV_ConceptoGastos
            .DefaultCellStyle.Font = New Font("Verdana", 10)
            .AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells
            .AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.DisplayedCells
            .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
            .AlternatingRowsDefaultCellStyle.BackColor = Color.AntiqueWhite
            .DataSource = dtConceptoG

            .Columns(0).HeaderText = "ID"
            .Columns(0).Visible = False
            .Columns(1).ReadOnly = True
            .Columns(1).HeaderText = "Nombre"
            .Columns(2).HeaderText = "Alias"
            .Columns(3).HeaderText = "Valor"
            .Columns(4).ReadOnly = True
            .Columns(4).HeaderText = "Grupo"
            .Refresh()

        End With

        desconectar()

    End Sub


    Sub ingresoConceptoGastos(ByVal nom As String, ByVal dalias As String, ByVal valor As String, ByVal igg As Integer)
        conectar()
        cmd = New OdbcCommand("INSERT INTO tconceptosdegastos (Nombre,Alias,Valor,IDGruposDeGastos) Values('" + nom + "','" + dalias + "','" + valor + "','" & igg & "')", con)

        If cmd.ExecuteNonQuery Then
            listaConceptoGastos()
            ActualizaValoresDeGrupos()
        Else
            MessageBox.Show(msjNoActualizar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)

        End If

        desconectar()
    End Sub

    Sub eliminaConceptoGastos(ByVal id As Integer)
        conectar()

        cmd = New OdbcCommand("DELETE FROM tconceptosdegastos WHERE ID = '" & id & "'", con)

        If cmd.ExecuteNonQuery Then
            listaConceptoGastos()
            ActualizaValoresDeGrupos()
        Else
            MessageBox.Show(msjNoEliminar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        desconectar()


    End Sub

    Sub actualizaConceptoGastos(ByVal vID As Integer, ByVal vNom As String, ByVal vAlias As String, ByVal vValor As Integer)

        conectar()
        cmd = New OdbcCommand("UPDATE tconceptosdegastos set Nombre='" + vNom + "',Alias='" + vAlias + "',Valor='" & vValor & "' WHERE ID = '" & vID & "'", con)

        If cmd.ExecuteNonQuery Then
            listaConceptoGastos()
            ActualizaValoresDeGrupos()
        Else
            MessageBox.Show(msjNoActualizar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        desconectar()


    End Sub
End Module
